package gov.dhs.cis.soa.readers;

import gov.dhs.cis.soa.Utils;

import java.io.InputStream;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public abstract class ExcelReader {
	static final Logger bLoger = Logger.getLogger(ExcelReader.class);

	private final String A2Z = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	
	public HSSFWorkbook openFile(String fileName) {
		InputStream stream = this.getClass().getClassLoader().getResourceAsStream(fileName);
		//File fileHandle = new File(stream);
		HSSFWorkbook w1 = null;
		try {
			w1 = new HSSFWorkbook(stream);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return w1;
	}

	public void closeAll(HSSFWorkbook w1) {
//		if(w1 != null)
//			w1.close();
	}
	
	private int convert2Num(String cellName) {
		if(Utils.isEmpty(cellName))
			return -1;
		int ret = 0;
		char[] toChars = cellName.toCharArray();
		for(int i = 0; i < toChars.length; i++) {
			int idx = A2Z.indexOf(toChars[i]);
			if(idx < 0) {
				bLoger.debug("Invalid character encountered : <" + cellName + ">");
				throw new RuntimeException("Invalid character encountered : <" + cellName + ">");
			}
			if(i > 0)
				ret += A2Z.length();
			ret += idx;
		}
		return ret;
	}

	public String getCellStringValue(HSSFWorkbook w, HSSFRow row, String cellName) {
		int nCellNum = convert2Num(cellName);
		return getCellStringValue(w, row.getCell(nCellNum));
	}

	public String getCellStringValue(HSSFWorkbook w, HSSFCell cellData) {
		String ret = "";
		
		if(cellData == null)
			return ret;
		
		if(HSSFCell.CELL_TYPE_FORMULA == cellData.getCellType()) {
			return ret;
		}
		if(HSSFCell.CELL_TYPE_NUMERIC == cellData.getCellType()) {
			ret = "" + getCellDoubleValue(cellData);
			int idx = ret.lastIndexOf(".0");
			if(idx > 0) {
				ret = ret.substring(0, idx);
			}
			return ret;
		}
		if(HSSFCell.CELL_TYPE_BOOLEAN == cellData.getCellType()) {
			ret = "" + cellData.getBooleanCellValue();
			return ret;
		}
		HSSFRichTextString strCell = cellData.getRichStringCellValue();
		if(strCell == null)
			return ret;
		HSSFFont font = cellData.getCellStyle().getFont(w);
		//if(font.getStrikeout())
		//	return ret;
		StringBuffer strBuf = new StringBuffer();
		char[] data = strCell.getString().toCharArray();
		short fntIdx = -1;
		for(short idx = 0; idx < data.length; idx++) {
			//fntIdx = strCell.getFontOfFormattingRun(idx);
			fntIdx = strCell.getFontAtIndex(idx);
			font = w.getFontAt(fntIdx);
			if(!font.getStrikeout())
				strBuf.append(data[idx]);
		}
		ret = strBuf.toString().trim();
		return ret;
	}
	
	public int getCellIntegerValue(HSSFWorkbook w, HSSFRow row, String cellName) {
		int nCellNum = convert2Num(cellName);
		int retValue = (int) getCellDoubleValue(row.getCell(nCellNum));
		return retValue;
	}
	
	public double getCellDoubleValue(HSSFCell cellData) {
		double ret = -1;
		if(cellData == null)
			return ret;
		try {
			ret = cellData.getNumericCellValue();
		} catch (Exception e) {
			ret = -1;
		}
		return ret;
	}	
}
